package com.example.link.mapper;

import com.example.link.entity.LinkAccessLog;
import com.example.link.model.AccessCount;
import com.example.link.model.stats.LinkStatsVO;
import org.apache.ibatis.annotations.*;

import java.util.List;
import java.util.Map;

/**
 * @author qwert
 * @version 1.0.0
 * @create 2025/8/28 21:17
 **/
@Mapper
public interface LinkAccessLogMapper {
    @Insert("""
INSERT INTO link_access_log (timestamp, short_url, ip, country, browser, browser_version, operating_system,
                             device_type, device_category, network_type)
VALUES (#{timestamp}, #{shortUrl}, #{ip}, #{country}, #{browser}, #{browserVersion},
 #{operatingSystem}, #{deviceType}, #{deviceCategory}, #{networkType});
""")
    void insertLinkAccessLog(LinkAccessLog linkAccessLog);


    @Select("""
<script>
    SELECT short_url AS shortUrl,
           COUNT(*) AS totalAccess,
           COUNT(DISTINCT ip) AS uniqueIPs,
           COUNT(DISTINCT CONCAT(ip, '|', browser, '|', operating_system, '|', country)) AS uniqueVisitors
    FROM link_access_log
    WHERE 1 = 1
        <if test="startTime != null and startTime != '' and endTime != null and endTime != ''">
            AND timestamp BETWEEN #{startTime} AND #{endTime}
        </if>
    GROUP BY short_url
</script>
""")
    @MapKey("shortUrl")
    Map<String, AccessCount> countAccessStats(@Param("startTime") Long startTime,
                                              @Param("endTime") Long endTime);
@Select("""
<script>
    SELECT short_url AS shortUrl,
           COUNT(*) AS totalAccess,
           COUNT(DISTINCT ip) AS uniqueIPs,
           COUNT(DISTINCT CONCAT(ip, '|', browser, '|', operating_system, '|', country)) AS uniqueVisitors
    FROM link_access_log
    WHERE short_url = #{shortUrl}
        <if test="startTime != null and startTime != '' and endTime != null and endTime != ''">
            AND timestamp BETWEEN #{startTime} AND #{endTime}
        </if>
</script>
""")
    AccessCount countAccessStatsByShortUrl(@Param("shortUrl") String shortUrl,
                                           @Param("startTime") Long startTime,
                                           @Param("endTime") Long endTime);

    @Select("""
      WITH RECURSIVE hours AS (SELECT 0 AS h UNION ALL\s
      SELECT h + 1 FROM hours WHERE h < 23)
      SELECT
          COALESCE(COUNT(l.timestamp), 0) AS cnt
      FROM hours h
      LEFT JOIN link_access_log l
          ON l.timestamp >= #{startTime} + h.h * 3600000
          AND l.timestamp < #{startTime} + (h.h + 1) * 3600000
          AND l.short_url = #{shortUrl}
      GROUP BY h.h
      ORDER BY h.h;
            """)
    List<Long> countTodayStatsWithHour(@Param("shortUrl") String shortUrl,
                                       @Param("startTime") long startTime);


    @Select("""
    WITH RECURSIVE date_range AS (
         SELECT #{startOfDayTimestamp} AS date
         UNION ALL
         SELECT date + 86400000
         FROM date_range
         WHERE date + 86400000 <= #{endOfDayTimestamp}
     )
     SELECT
         COALESCE(COUNT(l.timestamp), 0) AS pv,
         COALESCE(COUNT(DISTINCT l.ip), 0) AS uv,
         COALESCE(COUNT(DISTINCT CONCAT(l.ip, '|', l.browser, '|', l.operating_system, '|', l.country)), 0) AS uip,
         DATE_FORMAT(FROM_UNIXTIME(dr.date / 1000), '%Y-%m-%d') AS date
     FROM date_range dr
     LEFT JOIN link_access_log l
         ON DATE_FORMAT(FROM_UNIXTIME(l.timestamp / 1000), '%Y-%m-%d') = DATE_FORMAT(FROM_UNIXTIME(dr.date / 1000), '%Y-%m-%d')
         AND l.short_url = #{shortUrl}
         AND l.timestamp BETWEEN #{startOfDayTimestamp} AND #{endOfDayTimestamp}
     GROUP BY dr.date
     ORDER BY dr.date ASC
""")
    List<LinkStatsVO.DailyStats> countStatsWithDaily(@Param("shortUrl") String shortUrl,
                                                     @Param("startOfDayTimestamp") long startOfDayTimestamp,
                                                     @Param("endOfDayTimestamp") long endOfDayTimestamp);

    @Select("""
select browser, count(*) as count from link_access_log where short_url = #{shortUrl} group by browser;
""")
    List<LinkStatsVO.BrowserStats> countBrowserStats(String shortUrl);

    @Select("""
select operating_system as os, count(*) as count from link_access_log where short_url = #{shortUrl} group by operating_system;
""")
    List<LinkStatsVO.OsStats> countOsStats(String shortUrl);

    @Select("""
select device_type as device, count(*) as count from link_access_log where short_url = #{shortUrl} group by device_type;
""")
    List<LinkStatsVO.DeviceStats> countDeviceStats(String shortUrl);

    @Select("""
select network_type as network, count(*) as count from link_access_log where short_url = #{shortUrl} group by network_type;
""")
    List<LinkStatsVO.NetworkStats> countNetworkStats(String shortUrl);
}
